Amazon Redshift Spectrum 新たにサポートされたDATE型について試してみました

石川覚

2018.01.29

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

Amazon Redshift Spectrumを利用して、Amazon S3上ののテキストファイルやカラムナフォーマットファイル(Parquet)に保存されているDATE型のデータをクエリできるようになりました。AWSではリージョン毎にGlueデータカタログでテーブル定義を共有していますが、クエリエンジンやファイルフォーマット組み合わせによって、サポートしてしているデータ型が異なります。登録されたテーブル定義を共有するAmazon Athenaの関係についても解説します。検証したRedshiftのBuildは1/26に更新された1.0.1657です。（東京リージョンではまだデプロイされていないかもしれません。）

サポートしているDATE型・TIMESTAMP型の形式

まず、どんな形式の文字列をDATE型・TIMESTAMP型のデータとして読み取れるかについて解説します。Amazon Redshiftのデータのロード(COPYコマンド)では、一般的な形式は自動認識してロードできますが、Amazon Redshift Spectrum や Amazon Athenaはスキーマオンリードなので、形式が限定されるのはやむを得ません。もし認識できない形式の文字列の場合は値がNULLになります。

DATE型

DATE型がサポートしている形式は、ISO 8601の拡張形式(YYYY-MM-DD)です。ISO 8601の基本形式(YYYYMMDD)やExcelでよく見る形式(YYYY/MM/DD)はDATE型のデータとして読み込まれません。

TIMESTAMP型

TIMESTAMP型は、YYYY-MM-DD HH:MI:SS形式です。例えば2018-01-23 03:13:42は、ミリ秒が補完されて2018-01-23 03:13:42.000となります。2018-01-23 03:13:42.123456は、ミリ秒未満は切り捨てられて2018-01-23 03:13:42.123となります。

以降では、DATE型は2017-07-07、日時型(TIMESTAMP型)は2018-08-08 08:08:08を用いて検証します。

テキストファイル(タブ区切り)の検証

検証用データ

データファイルは以下のタブ区切り文字列(.tsv)です。

1 22.22 33.33 44.44 gogo0123456789 rokuroku 2017-07-07 2018-08-08 08:08:08

テーブル定義

このデータに対して、Amazon Athenaから以下のDDLでテーブル定義します。カラムに対してプリミティブなさまざまなデータ型設定しています。パーティションキーにもDATE型を設定しています。

CREATE EXTERNAL TABLE `test_datatypes`(
`id` int,
`price` decimal(10,2),
`profit` double,
`discount` float,
`name_full` varchar(100),
`name_short` char(10),
`order_date` date,
`order_timestamp` timestamp)
PARTITIONED BY (
`p_date` date)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://my_bucket/test_datatypes'
TBLPROPERTIES (
'classification'='csv',
'delimiter'='\t',
'transient_lastDdlTime'='1516946597',
'typeOfData'='file')

Amazon Redshift Spectrum からクエリを実行

従来のBuild1.0.1582では、「パーディションカラム以外DATE型はサポートしていないので、代わりにtimestamp型かvarchar型に定義してください」といったエラーが表示されます。

cmdb=> select version();
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1582
(1 row)

cmawsteamdb=> select * from my_schema_db.test_datatypes;
ERROR: Invalid DataCatalog response for external table "my_schema_db"."test_datatypes": Redshift does not support data type "date" for non-partitioning column "order_date". Try defining the column as "timestamp" or "varchar" instead.

執筆時点の最新版、Build1.0.1657では、DATE型のカラム(order_date)が表示されています。パーティションカラム(p_date)もDATE型として参照できました。

cmawsteamdb=# select version();
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1657
(1 row)

cmawsteamdb=# select * from my_schema_db.test_datatypes limit 1;
id | price | profit | discount | name_full | name_short | order_date | order_timestamp | p_date
----+-------+--------+----------+----------------+------------+------------+---------------------+------------
1 | 22.22 | 33.33 | 44.44 | gogo0123456789 | rokuroku | 2018-01-23 | 2018-01-23 03:13:42 | 2018-09-09
(1 row)

Amazon Athena からクエリを実行

もちろん、全く問題なくクエリが実行できました。

カラムナフォーマットファイル(Parquet)の検証

データソース test_datatypesテーブルからParquetのファイルの変換はAWS GlueをJobで生成しました。生成したファイルを同じくGlueのクローラでtest_datatypes_parquetテーブルを定義しています。

テーブル定義

Glueのクローラが生成したDDL以下のとおりです。

-- null: decimal(10,2), order_date, order_timestamp
CREATE EXTERNAL TABLE `test_datatypes_parquet`(
`id` int,
`price` decimal(10,2),
`profit` double,
`discount` float,
`name_full` string,
`name_short` string,
`order_date` date,
`order_timestamp` timestamp)
PARTITIONED BY (
`p_date` string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
's3://my_bucket/test_datatypes_parquet/'
TBLPROPERTIES (
'CrawlerSchemaDeserializerVersion'='1.0',
'CrawlerSchemaSerializerVersion'='1.0',
'UPDATED_BY_CRAWLER'='test_datatypes_parquet',
'averageRecordSize'='358',
'classification'='parquet',
'compressionType'='none',
'objectCount'='1',
'recordCount'='2',
'sizeKey'='4296',
'typeOfData'='file')

Amazon Redshift Spectrum からクエリを実行

cmawsteamdb=> select version();
version
--------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1582
(1 row)

cmawsteamdb=> select * from my_schema_db.test_datatypes_parquet limit 1;
ERROR: Invalid DataCatalog response for external table "my_schema_db"."test_datatypes_parquet": Redshift does not support data type "date" for non-partitioning column "order_date". Try defining the column as "timestamp" or "varchar" instead.

執筆時点の最新版、Build1.0.1657では、エラーメッセージに変化がありましたが、Parquetではエラーが発生しています。アナウンスがあったビルドはBuild 1.0.1636なので、その後の更新で変化があったのかもしれません。

cmawsteamdb=# select * from my_schema_db.test_datatypes_parquet limit 1;
ERROR: S3 Query Exception (Fetch)
DETAIL:
-----------------------------------------------
error: S3 Query Exception (Fetch)
code: 15001
context: Task failed due to an internal error. File 'https://s3-external-1.amazonaws.com/my_bucket/test_datatypes_parquet/p_date=2018-09-09/part-00000-b23fbb42-1628-43ba-9773-78130e375eef.snappy.parquet has an incompatible Parquet schema for column 's3://
query: 1902645
location: dory_util.cpp:678
process: query1_73 [pid=10900]
-----------------------------------------------

なお、Redshiftから以下のDDLを実行してテーブルを作成して、クエリを実行しましたが、動作に変化はありません。

CREATE EXTERNAL TABLE my_schema_db.test_datatypes_parquet_0 (
id integer,
price decimal(10,2),
profit double precision,
discount float,
name_full varchar(100),
name_short char(10),
order_date date,
order_timestamp timestamp
)
PARTITIONED BY (
p_date date)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
's3://my_bucket/test_datatypes_parquet/'
TABLE PROPERTIES (
'CrawlerSchemaDeserializerVersion'='1.0',
'CrawlerSchemaSerializerVersion'='1.0',
'UPDATED_BY_CRAWLER'='test_datatypes_parquet',
'averageRecordSize'='304',
'classification'='parquet',
'compressionType'='none',
'objectCount'='1',
'recordCount'='2',
'sizeKey'='3388',
'typeOfData'='file')

Amazon Athena からクエリを実行

Amazon AthenaのクエリエンジンであるPrestoは、カラムの型にDATE型やTIMESTAMP型をサポートしていませんので、NULLになります。

最後に

DATE型についてまとめますと、

DATEのフォーマットは、YYYY-MM-DD形式
TIMESTAMPのフォーマットは、YYYY-MM-DD HH:MI:SS形式
CSVとTSVとParquetは、パーティションキーにDATE型のデータをAmazon Redshift Spectrum と Amazon Athenaで参照できる（従来通りの仕様）
CSVとTSVは、カラムのDATE型のデータをAmazon Redshift Spectrum と Amazon Athena から参照できる（新たにサポート）
Parquetは、カラムのDATE型のデータをAmazon Redshift Spectrum と Amazon Athena から参照できない（新たにサポートされているはずだが、確認できなかった）
今回はCSVとTSVとParquet以外の他のファイルフォーマット（実際にはSerde）については検証していない

以上の結果から、新たにCSVとTSVは、カラムのDATE型を定義して、データをクエリできる事が確認できました。一方、残念ながら、アナウンスにあったParquetのカラムのDATE型は、データの参照ができませんでした。以下のフォーラムを見ると、一度は1.0.1636がリリースされたが、問題があり従来のBuild1.0.1582に戻され、その後1/26に今回検証した1.0.1657がリリースされたようです。そのような経緯があり1.0.1657は当初のリリースノートの内容とは異なるのかもしれません。今後サポートされるのを期待して待ちたいと思います。

Simple queries never finishing since patch 1.0.1636 was applied

以上の結果から、Amazon RedshiftのテーブルデータをCSVやTSVにUNLOADしたデータであれば、Amazon Redshift Spectrum や Amazon Athenaから共通で参照できる事も確認できました。今回のDATE型のサポートやヘッダ行のスキップのサポートによって、データレイクへのマイグレーションにはずみがつきそうです。